<?php
//数据库连接类
require_once '../common/RootPath.php';
RootPath::importPath("config/profile.inc.php");
RootPath::importPath("common/BeanUtils.php");
RootPath::importPath("common/UniqueIDGenerator.php");
RootPath::importPath("common/JsonResult.php");

class Database
{
    private $pdo;
    const add = "a"; //增加
    const update = "u"; //修改
    const delete = "d"; //删除
    const select = "s"; //查询

    public function __construct()
    {

        try {
            $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
            $this->pdo = new PDO($dsn, DB_USER, DB_PASS, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
            //echo "连接成功";
        } catch (PDOException $e) {
            //die("数据库连接失败：" . $e->getMessage());
        }

    }

    /**
     * 软删除方法
     * @param string $table 表名
     * @param int $id 主键 ID
     * @return bool 成功返回 true，否则返回 false
     */
    public function softDelete($table, $id)
    {
        $sql = "UPDATE $table SET is_deleted = 1  WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(":id", $id);
        $stmt->execute();

        $affectedRows = $stmt->rowCount();

        if ($affectedRows > 0) {
            $sql1 = "UPDATE $table SET deleted_at = NOW() WHERE id = :id";
            $stmt = $this->pdo->prepare($sql1);
            $stmt->bindParam(":id", $id);
            $stmt->execute();
        }
        //echo "受影响行数是：".$affectedRows;

        return JsonResult::DB2Json(null, null, $affectedRows, self::delete);

    }

    /**
     * 恢复软删除的数据
     * @param string $table 表名
     * @param int $id 主键 ID
     * @return bool 成功返回 true，否则返回 false
     */
    public function restore($table, $id)
    {
        $sql = "UPDATE $table SET is_deleted = 0, deleted_at = NULL WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $affectedRows = $stmt->rowCount();


        return JsonResult::DB2Json(null, null, $affectedRows, self::update);
    }

    /**
     * 查询未被软删除的数据
     * @param string $table 表名
     * @return array 查询结果
     */
    public function getActiveRecords($table)
    {
        $sql = "SELECT * FROM $table WHERE is_deleted = 0";
        $stmt = $this->pdo->query($sql);
        $arr = $stmt->fetchAll();
        $total = $this->pdo->query("SELECT FOUND_ROWS() AS total")->fetch(PDO::FETCH_ASSOC)['total'];
        return JsonResult::DB2Json($total, $arr, null, self::select);

    }

    //获取表名
    public function getTableName($sql)
    {
        if (preg_match('/FROM\s+`?([a-zA-Z0-9_]+)`?\s+/i', $sql, $matches)) {
            $table_name = $matches[1];
            return $table_name;
        }
    }

    //截取where条件
    public function join($sql)
    {
        preg_match('/(WHERE .*?)(\s*(ORDER BY|GROUP BY|LIMIT|$))/i', $sql, $matches);

        $whereClause = isset($matches[1]) ? trim($matches[1]) : '';

        return $whereClause;
    }


    //自定义sql语句带分页
    public function customSQLWithPage($sql)
    {
        $stmt = $this->pdo->query($sql);
        $list = $stmt->fetchAll(); // 获取所有数据

        $tableName = $this->getTableName($sql);
        $whereClause = $this->join($sql);

        $total_sql = "SELECT COUNT(*) AS total FROM $tableName $whereClause";
        $totalSta = $this->pdo->query($total_sql);
        $total_records = $totalSta->fetchColumn();

        return JsonResult::customQuery2Json($total_records,$list);

    }

    //单表
    public function customSQL($sql)
    {
        $stmt = $this->pdo->query($sql);
        $arr = $stmt->fetchAll(); // 获取所有数据

        $total = $this->pdo->query("SELECT FOUND_ROWS() AS total")->fetch(PDO::FETCH_ASSOC)['total'];
        return JsonResult::DB2Json($total,$arr,null,self::select);
    }


    //新增
    public function create($table, array $params)
    {

        $sonArrkey = "";
        $removedItems = [];
        foreach ($params as $keys => $values) {
            // echo $keys .':' .$values."\n";
            if (is_array($values)) {
                $sonArrkey = $keys;
                $removedItems[$keys] = $values;

                unset($params[$keys]);
            }
        }

        if(!empty($removedItems) && count($removedItems)>0){
            foreach ($removedItems[$sonArrkey] as $role) {
                $rid[] = $role->getRid();
            }

            $keyrid = implode(',', $rid);

            $arrs= array_merge($params, [$sonArrkey => $keyrid]);
        }else{
            $arrs= $params;
        }


        $keysArray = array();
        $valesArray = array();

        foreach ($arrs as $key => $value){
           //  echo $key .':' .$value."\n";
            $keysArray[] = $key;
            $valesArray[] = $value;
        }

        // 拼接字符串
        $keysresult = implode(',', $keysArray);

        //var_dump($keysresult);
        //$valesArray[0] = UniqueIDGenerator::generateSnowflakeID(1, 1);
        // 用 array_map 为每个元素添加单引号
        $quotedArray = array_map(function ($item) {
            return "'" . $item . "'";
        }, $valesArray);

        // 使用 implode 将数组拼接成字符串
        $qresult = implode(',', $quotedArray);

        // 输出结果
//        echo $qresult;

        // 去除尾部逗号（如果有的话）
        $keysresult = rtrim($keysresult, ',');

        // 输出结果
        //echo $keysresult;

        $sql = "INSERT INTO $table ($keysresult) VALUES ($qresult)";

        //echo $sql;


                $stmt = $this->pdo->prepare($sql);
                $result = $stmt->execute();

                $affectedRows = $stmt->rowCount();
                $lastInsertId = $this->pdo->lastInsertId();

                return JsonResult::DB2Json(null,null,$affectedRows,self::add);
        //return $this->pdo->lastInsertId();
    }

    //修改
    public function update($table, array $params)
    {

        $removedItems = [];

        foreach ($params as $keys => $values) {
            if (strpos($keys, 'id') !== false) {

                $removedItems[$keys] = $values;

                unset($params[$keys]);
            }
        }

        $keysArray = array();
        $valesArray = array();
        foreach ($params as $key => $value) {
            $keysArray[] = $key;
            $valesArray[] = $value;
        }

        $count = min(count($keysArray), count($valesArray));

        $sql = array();
        for ($i = 0; $i < $count; $i++) {
            $sql[] = $keysArray[$i] . '=' . '"' . $valesArray[$i] . '"';
        }
//        print_r($sql);

//        print_r($params);

        $filter = [];
        foreach ($removedItems as $key => $value) {
            $filter = $key . '=' . '"' . $value . '"';
        }


        $sqlresult = implode(',', $sql);
        $sqlresult = rtrim($sqlresult, ',');
        $sqlresult = ltrim($sqlresult, ',');

        echo $_sql = "UPDATE $table SET " . $sqlresult . " WHERE " . $filter;

        /*    $stmt = $this->pdo->prepare($_sql);

            $result = $stmt->execute();

            $affectedRows = $stmt->rowCount();
            if ($affectedRows>0) {
                echo "数据更新成功".$result.'条记录！';
                return JsonResult::DB2Json(null,null,$affectedRows,self::update);
            } else {
                echo "数据更新失败";
            }*/
    }

    //查询带分页
    public function queryWithPagination($table, $conditions = [], $orderBy = 'id ASC', $page = 1, $limit = 10) {
        $offset = ($page - 1) * $limit;
        $whereClause = '';
        $params = [];

        if (!empty($conditions)) {
            $whereParts = [];
            foreach ($conditions as $column => $value) {
                $whereParts[] = "`$column` = $value";
            }
            $whereClause = "WHERE " . implode(" AND ", $whereParts);
        }

        // 查询数据
        $sql = "SELECT * FROM `$table` $whereClause ORDER BY $orderBy LIMIT $limit OFFSET $offset";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $data = $stmt->fetchAll();

        // 计算总数
        $countSql = "SELECT COUNT(*) FROM `$table` $whereClause";
        $countStmt = $this->pdo->prepare($countSql);

        $countStmt->execute();
        $total = $countStmt->fetchColumn();

        return JsonResult::DB2Json($total, $data, null,self::select);
    }

    /**
     * 多对多查询
     * @param string $mainTable 主表名称
     * @param string $relationTable 中间表名称
     * @param string $relatedTable 关联表名称
     * @param string $mainTableKey 主表主键字段名
     * @param string $relationMainKey 中间表中指向主表的字段名
     * @param string $relationRelatedKey 中间表中指向关联表的字段名
     * @param string $relatedTableKey 关联表主键字段名
     * @param array $conditions 查询条件
     * @return array 查询结果
     */
    public function queryManyToMany(
        string $mainTable,
        string $relationTable,
        string $relatedTable,
        string $mainTableKey,
        string $relationMainKey,
        string $relationRelatedKey,
        string $relatedTableKey,
        array  $conditions = []
    ): array
    {
        $sql = "SELECT r.* 
                FROM $mainTable m
                INNER JOIN $relationTable rel ON m.$mainTableKey = rel.$relationMainKey
                INNER JOIN $relatedTable r ON rel.$relationRelatedKey = r.$relatedTableKey";

        // 动态添加查询条件
        $params = [];
        if (!empty($conditions)) {
            $whereClauses = [];
            foreach ($conditions as $key => $value) {
                $whereClauses[] = "$key = :$key";
                $params[":$key"] = $value;
            }
            $sql .= " WHERE " . implode(" AND ", $whereClauses);
        }

        echo $sql;
        /*  $stmt = $this->pdo->prepare($sql);
          $stmt->execute($params);

          return $stmt->fetchAll(PDO::FETCH_ASSOC);*/
    }

}

// 使用示例
//$db = new Database(DB_HOST, DB_NAME, DB_USER, DB_PASS);
//$db = new Database();

// 软删除用户 ID 为 1 的数据
//echo $db->softDelete('users', 72);

// 恢复用户 ID 为 1 的数据
//echo $db->restore('users', 72);

// 查询未被软删除的数据
//$activeUsers = $db->getActiveRecords('users');
//print_r($activeUsers);


/*$params = ['name'=>'jiangmingze','email'=>'101x83822371@qq.com'];
echo $db->create('users',$params);*/
/*
$example = new Example("jmz","1182550187@qq.com");
$arrx = BeanUtils::classToArrayWithPrivate($example);
$db->create('users',$arrx);
*/
/*$params = ['id'=>'79','name'=>'jmz','email'=>'79797971@qq.com'];
echo $db->update('users',$params);*/
